#!/usr/bin/python
# -*- coding:UTF-8 -*-
# @Time    : 2018/11/10 10:42
# @Author  : Aries
# @Site    : 
# @File    : crond_decoration_payment.py
# @Software: PyCharm
import os
import sys
import json
import MySQLdb
import datetime
from elasticsearch import Elasticsearch
import elasticsearch.helpers

class Espayment:

    def __init__(self, host='', username='', password='', media='', pthost='', esHost='', charset='utf8'):
        self.dbconn = None
        self.cursor = None
        self.flag = True

        self.host = host
        self.username = username
        self.password = password
        self.charset = charset
        self.media = media
        self.pthost = pthost
        self.esHost = esHost

        self.es = Elasticsearch(self.esHost)

        self.receiptType_Data = {}

        self.result = []

        # 装修 SQL
        self.decoration_column = u"""
            pd.company,
            pd.orderId,
            pdr.collectTime  AS `收款时间`,
            pdr.cluid AS `收款人uid`,
            pdr.collecterName AS `收款人名`,
            pdr.cash AS `现金5`,
            pdr.cashInfo AS `现金信息`,
            pdr.bank  AS `刷卡7`,
            pdr.bankInfo AS `刷卡信息`,
            pdr.checkv  AS `转账9`,
            pdr.checkvBank AS `转账银行信息`,
            pdr.installment  AS `分期11`,
            pdr.installmentBank AS `分期银行信息`,
            pdr.accounts  AS `对公13`,
            pdr.accountsBank AS `对公银行信息`,
            pdr.coupon  AS `卷值`,
            pdr.couponInfo AS `卷值信息`,
            pdr.advance  AS `预付款`,
            pdr.advanceType  AS `预付款类型1合同2工程`,
            pd.paymentType AS `款项类型/类别2`
        """
        self.DECORATION_SQL = u""" 
            SELECT
              %s
            FROM
                payment.decorationpayment AS pd,
                payment.decorationreceipt AS pdr
            where
                pd.drId = pdr.drId
                AND pdr.collectTime >= '%s 00:00:00'
                AND pdr.collectTime <= '%s 23:59:59'
                %s
        """

        # 主材 SQL
        self.material_column = u"""
            mm.orderId AS `订单号`,
            mm.company AS `公司id`,
            mp.paymentType AS `款项类型/类别`,
            mr.collectTime AS `凭证收款时间`,
            mr.cash AS `现金`,
            mr.cashInfo AS `现金信息`,
            mr.bank AS `刷卡`,
            mr.bankInfo AS `刷卡信息`,
            mr.checkv AS `转账`,
            mr.checkvBank AS `转账银行信息`,
            mr.installment AS `分期`,
            mr.installmentBank AS `分期银行信息`,
            mr.accounts AS `对公`,
            mr.accountsBank AS `对公银行信息`,
            mr.coupon AS `卷值 `,
            mr.couponInfo AS `卷值信息`,
            mr.advance AS `预付款`,
            mr.advanceType AS `预付款类型1合同2工程`,
            mr.collecterName AS `收款人姓名`,
            mr.cluid AS `收款人uid`
        """
        self.MATERIAL_SQL = u"""
            SELECT 
                %s
            FROM
                payment.materialpayment AS mp,
                payment.materialreceipt AS mr,
                material.material AS mm
            where 
                mp.mrId = mr.mrId
            AND mm.mid = mp.mid
            AND mr.collectTime >= '%s 00:00:00'
            AND mr.collectTime <= '%s 23:59:59'
            %s
        """

        # 销售 SQL
        self.sales_colum = u"""
            ss.orderId AS `订单号`,
            sp.company AS `公司id`,
            sp.type AS `款项类型/类别`,
            sr.collectTime AS `凭证收款时间`,
            sr.cash AS `现金`,
            sr.cashInfo AS `现金信息`,
            sr.bank AS `刷卡`,
            sr.bankInfo AS `刷卡信息`,
            sr.checkv AS `转账`,
            sr.checkvBank AS `转账银行信息`,
            sr.installment AS `分期`,
            sr.installmentBank AS `分期银行信息`,
            sr.accounts AS `对公`,
            sr.accountsBank AS `对公银行信息`,
            sr.coupon AS `卷值 `,
            sr.couponInfo AS `卷值信息`,
            sr.advance AS `预付款`,
            sr.advanceType AS `预付款类型1合同2工程`,
            sr.collecterName AS `收款人`,
	        sr.cluid AS `收款人ID`
        """
        self.SALES_SQL = u"""
        SELECT 
            %s
        FROM
            payment.salespayment AS sp,
            payment.salesreceipt AS sr,
            sales.sales AS ss
        where
            sp.sid = ss.sid
        AND sp.srid = sr.srid
        AND sr.collectTime >= '%s 00:00:00'
        AND sr.collectTime <= '%s 23:59:59'
        %s
        """

        # 预付 SQL
        self.advance_column = u"""
            p.company,
            p.orderId AS `装修单号`,
            pad.collectTime AS `收款时间`,
            p.type AS `类型`,
            p.paymentType AS `款项类型`,
            pad.cluid AS `收款人uid`,
            pad.collecterName AS `收款人姓名`,
            pad.cash AS `现金`,
            pad.cashInfo AS `现金信息`,
            pad.bank AS `刷卡`,
            pad.bankInfo AS `刷卡信息`,
            pad.checkv AS `转账`,
            pad.checkvBank AS `转账信息`,
            pad.installment AS `分期`,
            pad.installmentBank AS `分期信息`,
            pad.accounts AS `对公`,
            pad.accountsBank AS `对公银行`
        """
        self.ADVANCE_SQL = u"""
        SELECT 
            %s
        FROM
            payment.payment AS p,
            payment.advancepayment AS pa,
            payment.advancereceipt AS pad
        WHERE
            p.arId = pad.arId
        AND p.customerId = pa.customerId
        AND pad.collectTime >= '%s 00:00:00'
        AND pad.collectTime <= '%s 23:59:59'
        %s
        """

    def fmtTime(self, collectTime):
        return datetime.datetime.strptime(collectTime.strftime('%Y-%m-%d %H:%M:%S'), "%Y-%m-%d %H:%M:%S") - datetime.timedelta(hours=8)

    # 装修款
    def decorationPage(self, startTime, endTime, company):
        flag = True
        nowPage = 1

        company = self.company(u"pd.company", company)

        sql = self.DECORATION_SQL % (self.decoration_column, startTime, endTime, company)

        while (flag):
            data = self.page(sql, nowPage, size=200)
            self.result = []
            for i, v in enumerate(data):
                company = int(v[0])
                orderId = v[1]
                collectTimes = v[2]  # `收款时间`
                collectTime = self.fmtTime(collectTimes)
                cluid = v[3]  # `收款人uid`
                collecterName = v[4]  # `收款人名`
                cash = float(v[5])  # `现金5`
                cashInfo = str(v[6])  # `现金信息`
                bank = float(v[7])  # `刷卡7`
                bankInfo = v[8]  # `刷卡信息`
                checkv = float(v[9])  # `转账9`
                checkvBank = str(v[10])  # `转账银行信息`
                installment = float(v[11])  # `分期11`
                installmentBank = str(v[12])  # `分期银行信息`
                accounts = float(v[13])  # `对公13`
                accountsBank = str(v[14])  # `对公银行信息`
                coupon = float(v[15])  # `卷值`
                couponInfo = v[16]  # `卷值信息`
                advance = float(v[17])  # `预付款`
                advanceType = v[18]  # `预付款类型1合同2工程`
                paymentType = v[19]  # `类型
                bigType = u"装修款"

                flag = True
                if paymentType.find(u"退款") != -1 or paymentType.find(u"扣款") != -1 or paymentType.find(u"减项款") != -1:
                    flag = False

                # 现金
                if cash != 0:
                    self.cash(bigType, cash, collectTime, cashInfo, collecterName, cluid, company, orderId, flag)

                # 刷卡
                if bank != 0:
                    self.bank(bigType, bank, collectTime, bankInfo, collecterName, cluid, company, orderId, flag)

                # 转账
                if checkv != 0:
                    self.checkv(bigType, checkv, collectTime, checkvBank, collecterName, cluid, company, orderId, flag)

                # 卷
                if coupon != 0:
                    self.coupon(bigType, coupon, collectTime, couponInfo, collecterName, cluid, company, orderId, flag)

            elasticsearch.helpers.bulk(self.es, self.result, request_timeout=60)

            nowPage += 1
            if len(data) < 200:
                flag = False

    # 主材款
    def materialPage(self, startTime, endTime, company):
        flag = True
        nowPage = 1

        company = self.company(u"mm.company", company)

        sql = self.MATERIAL_SQL % (self.material_column, startTime, endTime, company)

        while (flag):
            data = self.page(sql, nowPage, size=200)
            self.result = []
            for i, v in enumerate(data):
                orderId = v[0]  # `订单号`
                company = int(v[1])  # `公司id`
                paymentType = int(v[2])  # `款项类型/类别`
                collectTimes = v[3]  # `凭证收款时间`
                collectTime = self.fmtTime(collectTimes)
                cash = float(v[4])  # `现金`
                cashInfo = v[5]  # `现金信息`
                bank = float(v[6])  # `刷卡`
                bankInfo = v[7]  # `刷卡信息`
                checkv = float(v[8])  # `转账`
                checkvBank = v[9]  # `转账银行信息`
                installment = float(v[10])  # `分期`
                installmentBank = v[11]  # `分期银行信息`
                accounts = float(v[12])  # `对公`
                accountsBank = v[13]  # `对公银行信息`
                coupon = float(v[14])  # `卷值 `
                couponInfo = v[15]  # `卷值信息`
                advance = float(v[16])  # `预付款`
                advanceType = v[17]  # `预付款类型1合同2工程`
                collecterName = v[18]  # 收款人姓名
                cluid = v[19]  # 收款人uid

                bigType = u"主材款"

                flag = True
                if paymentType in [4, 5, 6, 7]:
                    flag = False

                # 现金
                if cash != 0:
                    self.cash(bigType, cash, collectTime, cashInfo, collecterName, cluid, company, orderId, flag)

                # 刷卡
                if bank != 0:
                    self.bank(bigType, bank, collectTime, bankInfo, collecterName, cluid, company, orderId, flag)

                # 转账
                if checkv != 0:
                    self.checkv(bigType, checkv, collectTime, checkvBank, collecterName, cluid, company, orderId, flag)

                # 卷
                if coupon != 0:
                    self.coupon(bigType, coupon, collectTime, couponInfo, collecterName, cluid, company, orderId, flag)

            elasticsearch.helpers.bulk(self.es, self.result, request_timeout=60)
            nowPage += 1
            if len(data) < 200:
                flag = False

    # 销售
    def salesPage(self, startTime, endTime, company):
        flag = True
        nowPage = 1

        company = self.company(u"sp.company", company)

        sql = self.SALES_SQL % (self.sales_colum, startTime, endTime, company)

        while (flag):
            data = self.page(sql, nowPage, size=200)
            self.result = []
            for i, v in enumerate(data):
                orderId = v[0]  # `订单号`
                company = int(v[1])  # `公司id`
                type = v[2]  # `款项类型/类别`
                collectTimes = v[3]  # `凭证收款时间`
                collectTime = self.fmtTime(collectTimes)
                cash = float(v[4])  # `现金`
                cashInfo = v[5]  # `现金信息`
                bank = float(v[6])  # `刷卡`
                bankInfo = v[7]  # `刷卡信息`
                checkv = float(v[8])  # `转账`
                checkvBank = v[9]  # `转账银行信息`
                installment = float(v[10])  # `分期`
                installmentBank = v[11]  # `分期银行信息`
                accounts = float(v[12])  # `对公`
                accountsBank = v[13]  # `对公银行信息`
                coupon = float(v[14])  # `卷值 `
                couponInfo = v[15]  # `卷值信息`
                advance = float(v[16])  # `预付款`
                advanceType = v[17]  # `预付款类型1合同2工程`
                collecterName = v[18]  # 收款人
                cluid = v[19]  # 收款人ID
                bigType = u"销售款"

                flag = True
                if type in [4, 5, 6, 7]:
                    flag = False

                # 现金
                if cash != 0:
                    self.cash(bigType, cash, collectTime, cashInfo, collecterName, cluid, company, orderId, flag)

                # 刷卡
                if bank != 0:
                    self.bank(bigType, bank, collectTime, bankInfo, collecterName, cluid, company, orderId, flag)

                # 转账
                if checkv != 0:
                    self.checkv(bigType, checkv, collectTime, checkvBank, collecterName, cluid, company, orderId, flag)

                # 卷
                if coupon != 0:
                    self.coupon(bigType, coupon, collectTime, couponInfo, collecterName, cluid, company, orderId, flag)

            elasticsearch.helpers.bulk(self.es, self.result, request_timeout=60)
            nowPage += 1
            if len(data) < 200:
                flag = False

    # 预付
    def paymentPage(self, startTime, endTime, company):
        flag = True
        nowPage = 1

        company = self.company(u"p.company", company)

        sql = self.ADVANCE_SQL % (self.advance_column, startTime, endTime, company)

        while (flag):
            data = self.page(sql, nowPage, size=200)
            self.result = []
            for i, v in enumerate(data):
                company = int(v[0])  # 公司
                orderId = v[1]  # `装修单号`
                collectTimes = v[2]  # `收款时间`
                collectTime = self.fmtTime(collectTimes)
                type = v[3]  # `类型`,
                paymentType = v[4]  # `款项类型`
                cluid = v[5]  # `收款人uid`
                collecterName = v[6]  # `收款人姓名`
                cash = float(v[7])  # `现金`
                cashInfo = v[8]  # `现金信息`
                bank = float(v[9])  # `刷卡`
                bankInfo = v[10]  # `刷卡信息`
                checkv = float(v[11])  # `转账`
                checkvBank = v[12]  # `转账信息`
                installment = float(v[13])  # `分期`
                installmentBank = v[14]  # `分期信息`
                accounts = float(v[15])  # `对公`
                accountsBank = v[16]  # `对公银行`

                bigType = u"预付款"
                flag = True
                if type == 0:
                    flag = False

                # 现金
                if cash != 0:
                    self.cash(bigType, cash, collectTime, cashInfo, collecterName, cluid, company, orderId, flag)

                # 刷卡
                if bank != 0:
                    self.bank(bigType, bank, collectTime, bankInfo, collecterName, cluid, company, orderId, flag)

                # 转账
                if checkv != 0:
                    self.checkv(bigType, checkv, collectTime, checkvBank, collecterName, cluid, company, orderId,
                                flag)

            elasticsearch.helpers.bulk(self.es, self.result, request_timeout=60)

            nowPage += 1
            if len(data) < 200:
                flag = False

    # 现金--通用
    def cash(self, bigType, money, collectTime, cashInfo, collecterName, cluid, company, oerderId, flag):

        receiptType = self.getReceiptType(cashInfo, company)
        if receiptType == None:
            name = u""  # 名称
            no = u""  # 编号
        else:
            name = receiptType[1]  # 名称
            no = u"%s" % cashInfo  # 编号
        money = money if flag  else -money
        template = {
            u"no": no,
            u"name": name,
            u"money": money,
            u"collectTime": collectTime,
            u"collecterName": collecterName,
            u"userId": cluid,
            u"company": company,
            u"bigType": bigType,
            u"smallType": u"现金"
        }

        options = {
            u"_op_type": u"index",
            u"_index": u"payment-%s" % self.nowTime()[0:4],
            u"_type": u"payment",
            u"_source": template
        }
        self.result.append(options)

    # 刷卡--通用
    def bank(self, bigType, money, collectTime, bankInfo, collecterName, cluid, company, oerderId, flag):
        banks = bankInfo.split("#")

        for _bank in banks:

            if _bank == "": continue

            _info = _bank.split("|")

            _no = _info[0]  # 编号
            _money = float(_info[1])
            _time = _info[2][0:19]
            _time = datetime.datetime.strptime(_time, '%Y-%m-%d %H:%M:%S')
            _time = datetime.datetime.strptime(_time.strftime('%Y-%m-%d %H:%M:%S'),"%Y-%m-%d %H:%M:%S") - datetime.timedelta(hours=8)
            receiptType = self.getReceiptType(_no, company)
            if receiptType == None:
                name = u""
                _no = u""
            else:
                name = receiptType[1]  # 名称
            money = money if flag  else -money

            template = {
                u"no": _no,
                u"name": name,
                u"money": _money,
                u"collectTime": _time,
                u"collecterName": collecterName,
                u"userId": cluid,
                u"company": company,
                u"bigType": bigType,
                u"smallType": u"刷卡"
            }
            options = {
                u"_op_type": u"index",
                u"_index": u"payment-%s" % self.nowTime()[0:4],
                u"_type": u"payment",
                u"_source": template
            }
            self.result.append(options)

    # 转账--通用
    def checkv(self, bigType, money, collectTime, checkvBank, collecterName, cluid, company, oerderId, flag):
        if checkvBank.find(u"#") != -1:
            # 包含#号
            checkvBanks = checkvBank.split("#")
            for _checkvBanks in checkvBanks:
                if _checkvBanks == "":
                    continue
                _info = _checkvBanks.split("|")

                _no = _info[0]
                _money = float(_info[1])
                _time = _info[2]

                _time = datetime.datetime.strptime(_time, '%Y-%m-%d %H:%M:%S')
                _time = self.fmtTime(_time)
                receiptType = self.getReceiptType(_no, company)
                if receiptType == None:
                    name = u""
                    _no = u""
                else:
                    name = receiptType[1]
                money = money if flag  else -money

                template = {
                    u"no": _no,
                    u"name": name,
                    u"money": _money,
                    u"collectTime": _time,
                    u"collecterName": collecterName,
                    u"userId": cluid,
                    u"company": company,
                    u"bigType": bigType,
                    u"smallType": u"转账"
                }
                options = {
                    u"_op_type": u"index",
                    u"_index": u"payment-%s" % self.nowTime()[0:4],
                    u"_type": u"payment",
                    u"_source": template
                }
                self.result.append(options)

        else:
            # 不包含#号 老数据
            receiptType = self.getReceiptType(checkvBank, company)
            if receiptType == None:
                name = u""
                checkvBank = u""
            else:
                name = receiptType[1]  # 名称

            money = money if flag  else -money

            template = {
                u"no": checkvBank,
                u"name": name,
                u"money": money,
                u"collectTime": collectTime,
                u"collecterName": collecterName,
                u"userId": cluid,
                u"company": company,
                u"bigType": bigType,
                u"smallType": u"转账"
            }

            options = {
                u"_op_type": u"index",
                u"_index": u"payment-%s" % self.nowTime()[0:4],
                u"_type": u"payment",
                u"_source": template
            }
            self.result.append(options)

    # 优惠卷--通用
    def coupon(self, bigType, money, collectTime, couponInfo, collecterName, cluid, company, oerderId, flag):
        coupons = couponInfo.split("#")

        for _coupon in coupons:

            if _coupon == "": continue

            _info = _coupon.split(":")
            _no = _info[0]
            _money = float(_info[1])

            receiptType = self.getReceiptType(_no, company)
            if receiptType == None:
                name = u""
                _no = u""
            else:
                name = receiptType[1]  # 名称
            money = money if flag  else -money

            template = {
                u"no": _no,
                u"name": name,
                u"money": _money,
                u"collectTime": collectTime,
                u"collecterName": collecterName,
                u"userId": cluid,
                u"company": company,
                u"bigType": bigType,
                u"smallType": u"优惠卷"
            }

            options = {
                u"_op_type": u"index",
                u"_index": u"payment-%s" % self.nowTime()[0:4],
                u"_type": u"payment",
                u"_source": template
            }
            self.result.append(options)

    # 缓存receipttype收据类型
    def cacheReceiptType(self):
        sql = u"SELECT * FROM communion.receipttype"
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        for i, v in enumerate(data):
            no = v[2]
            company = int(v[10])
            keyName = u"%s-%s" % (no, company)

            self.receiptType_Data[keyName] = v

    # 通过 no company获得receipttype收据类型数据
    def getReceiptType(self, no, company):
        keyName = u"%s-%s" % (no, int(company))
        if self.receiptType_Data.has_key(keyName):
            return self.receiptType_Data[keyName]

    #数据库连接---关闭
    def openDBConnect(self):
        self.dbconn = MySQLdb.connect(self.host, self.username, self.password, charset=self.charset)
    def getDBCursor(self):

        if not self.dbconn:
            self.openDBConnect()

        self.cursor = self.dbconn.cursor()
    def closeDBConnect(self):
        if self.dbconn:
            self.dbconn.close()
            self.dbconn = None

    #分页
    def page(self, sql, nowPage, size=200):

        nowPage = (nowPage - 1) * size

        limtStr = """limit %s,%s; """ % (nowPage, size)
        pageSql = sql + limtStr

        self.cursor.execute(pageSql)
        data = self.cursor.fetchall()

        return data

    # @功能,返回当前时间, 格式为 %Y-%m-%d %H:%M:%S
    def nowTime(self):
        nowTime = datetime.datetime.now()
        strNowTime = nowTime.strftime("%Y-%m-%d %H:%M:%S")
        return strNowTime

    #公司
    def company(self, cloumCompany,company):

        if company != 0:
            companyId = u"AND %s = %s" % (cloumCompany, company)
        else:
            companyId = u" "

        return companyId

    def time(self, startTime, endTime, num=31):
        if type(startTime) != datetime.datetime:
            if startTime.find(u"/") != -1:
                startTime = startTime.replace("/", "-")
                endTime = endTime.replace("/", "-")

            if len(startTime) == 10:
                startTime = "%s 00:00:00" % startTime
                endTime = "%s 23:59:59" % endTime

            startTime = datetime.datetime.strptime(startTime, '%Y-%m-%d %H:%M:%S')
            endTime = datetime.datetime.strptime(endTime, '%Y-%m-%d %H:%M:%S')

        if endTime - startTime <= datetime.timedelta(days=num):
            return True
        else:
            return False

    def main(self, startTime, endTime, company):

        falg = self.time(startTime, endTime)
        if falg == False:
            raise RuntimeError(u'时间范围不在1-31天之内')

        if startTime=="" or endTime=="":
            return
        self.getDBCursor()
        self.cacheReceiptType()

        self.decorationPage(startTime, endTime, company)
        self.materialPage(startTime, endTime, company)
        self.paymentPage(startTime, endTime, company)
        self.salesPage(startTime, endTime, company)

        self.closeDBConnect()
        sys.exit(0)

if __name__ == '__main__':
    """
    按 装修/主材/销售/预付 款项收据拆分出 现金/刷卡/转账/券
    每个方式对应  编号/名称/大类(装修)/小类(现金)/金额/收款时间/收款人uid/收款人名/公司
    
    Kibana 中 Index Patterns :payment-年份 例: payment-2018
    使用控制:
    python crond_decoration_payment.py args company
    1.自动获取昨天时间
    python crond_decoration_payment.py auto 1
    2.指定时间
    python crond_decoration_payment.py 2018/10/01-2018/11/31 1
    
    """
    espayment = Espayment
    modernFilePath = os.path.realpath(__file__)
    configPath = "%s/%s" % (os.path.dirname(modernFilePath), "config.json")

    config = json.load(open(configPath))
    dbconf = config.get("database")

    args = sys.argv[1]
    company = sys.argv[2]

    startTime = ""
    endTime = ""

    if args == "auto":
        nowTime = datetime.datetime.now()
        strNowTime = nowTime.strftime("%Y-%m-%d %H:%M:%S")

        startTime = str(datetime.datetime.now() - datetime.timedelta(days=1))[0:10]
        endTime = startTime
    else:
        spargs = args.split("-")
        startTime = spargs[0]
        endTime = spargs[1]

    espayment = Espayment(dbconf.get("host"), dbconf.get("username"), dbconf.get("password"), config.get("media"),config.get("pthost"), config.get("eshost"))
    espayment.main(startTime, endTime, int(company))
